CREATE TABLE [RequestType](
	reqTypeID int IDENTITY,
	description varchar(50) NOT NULL,
	PRIMARY KEY (reqTypeID)
)

CREATE TABLE [Request](
	requestID int IDENTITY,
	userID int NOT NULL,
	reqTypeID int NOT NULL,
	dateReq datetime NOT NULL,
	urgent char(1) NOT NULL,
	status char(1) NOT NULL,
	PRIMARY KEY (requestID),
	FOREIGN KEY (userID) REFERENCES [User] (userID),
	FOREIGN KEY (reqTypeID) REFERENCES [RequestType] (reqTypeID) 
)

CREATE TABLE [Orders](
	orderID int NOt IDENTITY,
	requestID int NOT NULL,	
	orgID int NOT NULL,
        userID int NOT NULL,
	creationDate datetime NOT NULL,
	deliveryDate datetime NOT NULL,
	createdByUserName varchar(50) NOT NULL,
	status char(1) NOT NULL,
	PRIMARY KEY (orderID),
	FOREIGN KEY (requestID) REFERENCES [Request] (requestID),
	FOREIGN KEY (orgID) REFERENCES [Organization] (orgID),
	FOREIGN KEY (userID) REFERENCES [User] (userID)  
)

CREATE TABLE [OrderItem](
	orderItemID int NOT NULL,
	orderID int NOT NULL,
	itemID int NOT NULL,
	qtd float NOT NULL,
	price float NOT NULL,
	PRIMARY KEY (orderItemID),
	FOREIGN KEY (orderID) REFERENCES [Orders] (orderID),
	FOREIGN KEY (itemID) REFERENCES [Item] (itemID)
);
